IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCOIUserList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCOIUserList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************      
* Name:   [GetCOIUserList]      
* Purpose:  Retrieves matching rows joining with passed in admPersonIds.       
*      
* PARAMETERS(IN)      
* Name          Description           
* -------------      -------------------------------------------      
* @xml       xml Document containing list of Ids(REQUIRED)

 The AdmPersonId is contained in the XML which has the following form:    
  <NewDataSet>    
   <Table>    
    <AdmPersonId>23471</AdmPersonId>    
    </Table>    
   <Table>    
    <AdmPersonId>19184</AdmPersonId>    
   </Table>    
   ...    
  </NewDataSet>    
    
If the XML contains additional elements as siblings of the AdmPersonId element, they will be ignored.    
If the XML contains siblings of the Table element, they will be ignored.    
The AdmPersonIds from the XML will be loaded into a table variable so that an inner join may    
be done with that table to limit the resultset to the specified AdmPersonIds.    
    
Note that the table variable is sequenced in the same order as the COI IDs are specified in the    
XML. This preserves any sorting sequence that had already been applied to the list.            
*********************************************************************/      
      
CREATE PROCEDURE [dbo].[GetCOIUserList]      
(      
@xml text      
)      
AS BEGIN      
SET NOCOUNT ON      
      
	DECLARE @xmlDoc INT
	DECLARE @admPersonIds TABLE (SEQUENCE INT IDENTITY, [ID] INT,HierarchyTypeId INT)        
	EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xml        
	INSERT INTO @admPersonIds SELECT [Id],[HierarchyTypeId] FROM openxml(@xmlDoc, '//IdList', 2) WITH (Id INT,HierarchyTypeId INT)        
	EXEC sp_xml_removedocument @xmlDoc      
      
	SELECT DISTINCT
	adp.[Id],adp.[lastname]+', '+adp.[firstname] as [Name],fdu.[UserName],adp.[OutsideMember],adp.[COIRole],COIHMap.HierarchyTypeId  
	FROM @admPersonIds temp  
	LEFT JOIN COI_Hierarchy_Map COIHMap ON COIHMap.ChildId = temp.[Id] AND COIHMap.HierarchyTypeId = temp.HierarchyTypeId
	LEFT JOIN AdmPerson adp ON adp.[Id]= temp.[Id]  
	LEFT JOIN FWkDomainUser fdu ON fdu.[Id]=adp.[FWKDomainUserId]  
      
END      

--select * from coi_Hierarchy_map where HierarchyTypeId = 0